package com.libweb.model.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.libweb.model.dto.Board;
import com.libweb.model.dto.Member;

public class MemberDao {
	
	public void insertMember(Member member) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			//1. 드라이버 로딩
			Class.forName("com.mysql.jdbc.Driver");
			//2. 연결 생성
			conn = DriverManager.getConnection("jdbc:mysql://172.16.3.13:3306/libraryweb",	//url
					"instructor", "mysql");													//account
			
			
			//3. 명령 생성
			String sql = "INSERT INTO member "
					+ "(memberid, passwd, name, address, phoneno) "	//아이디 비밀번호 이름 주소 전화번호
					+ "VALUES (?, ?, ?, ?, ?);";	
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, member.getMemberId());
			pstmt.setString(2, member.getPasswd());
			pstmt.setString(3, member.getName());
			pstmt.setString(4, member.getAddress());
			pstmt.setString(5, member.getPhoneNo());
			//4. 명령 실행
			pstmt.executeUpdate();
		}catch (Exception ex) {
			
		}finally {
			try { pstmt.close(); } catch(Exception ex) {}
			try { conn.close(); } catch(Exception ex) {}
		}		
	}
	
	public List<Member> getMembers() {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Member> members = new ArrayList<Member>();
		
		try {
			//2. 연결 생성
			conn = ConnectionHelper.getConnection();
			//3. 명령 생성
			String sql = "SELECT memberid, name, address, phoneno, regdate From member Order by regdate";
			pstmt = conn.prepareStatement(sql);
			//4. 명령 실행
			rs = pstmt.executeQuery();
			//5. 조회 결과를 처리 (SELECT QUERY인 경우)
			while(rs.next()) {
				Member member = new Member();
				member.setMemberId(rs.getString(1));
				member.setName(rs.getString(2));
				member.setAddress(rs.getString(3));
				member.setPhoneNo(rs.getString(4));
				member.setRegDate(rs.getDate(5));
				
				members.add(member);
			}
		}catch (Exception ex) {
			ex.printStackTrace();
		}finally {
			try { rs.close(); } catch(Exception ex) {}
			try { pstmt.close(); } catch(Exception ex) {}
			try { conn.close(); } catch(Exception ex) {}
		}
		return members;
	}
	
	public Member getMemberById(String id) {
			
			Connection conn = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			Member member = null;
			
			try {
				//2. 연결 생성
				conn = ConnectionHelper.getConnection();
				//3. 명령 생성
				String sql = "SELECT memberid, name, address, phoneno, regdate From member WHERE memberid = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,  id);
				//4. 명령 실행
				rs = pstmt.executeQuery();
				//5. 조회 결과를 처리 (SELECT QUERY인 경우)
				if (rs.next()) {
					member = new Member();
					member.setMemberId(rs.getString(1));
					member.setName(rs.getString(2));
					member.setAddress(rs.getString(3));
					member.setPhoneNo(rs.getString(4));
					member.setRegDate(rs.getDate(5));
				}
			}catch (Exception ex) {
				ex.printStackTrace();
			}finally {
				try { rs.close(); } catch(Exception ex) {}
				try { pstmt.close(); } catch(Exception ex) {}
				try { conn.close(); } catch(Exception ex) {}
			}
			return member;
		}
	
	public Member getMemberByIdAndPasswd(String id, String passwd) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Member member = null;
		
		try {
			//2. 연결 생성
			conn = ConnectionHelper.getConnection();
			//3. 명령 생성
			String sql = "SELECT memberid, name, address, phoneno, regdate From member " +
						"WHERE memberid = ? AND passwd = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			pstmt.setString(2, passwd);
			//4. 명령 실행
			rs = pstmt.executeQuery();
			//5. 조회 결과를 처리 (SELECT QUERY인 경우)
			if (rs.next()) {
				member = new Member();
				member.setMemberId(rs.getString(1));
				member.setName(rs.getString(2));
				member.setAddress(rs.getString(3));
				member.setPhoneNo(rs.getString(4));
				member.setRegDate(rs.getDate(5));
			}
		}catch (Exception ex) {
			ex.printStackTrace();
		}finally {
			try { rs.close(); } catch(Exception ex) {}
			try { pstmt.close(); } catch(Exception ex) {}
			try { conn.close(); } catch(Exception ex) {}
		}
		return member;
	}
	
	public void deleteMember(String id) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
						
			String sql = "DELETE FROM member WHERE memberid = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			
			pstmt.executeUpdate();
						
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			//6. 연결닫기
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}		
	}
	
	public int updateMember(Member member) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		int count = 0;
		try {
			conn = ConnectionHelper.getConnection();
			String sql = 
				"UPDATE member " +
				"SET name = ?, phoneno = ?, address = ? " + 
				"WHERE memberid = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, member.getName());
			pstmt.setString(2, member.getPhoneNo());
			pstmt.setString(3, member.getAddress());
			pstmt.setString(4, member.getMemberId());
			count = pstmt.executeUpdate();
		} catch (Exception ex) {
			count = 0;
			ex.printStackTrace();
		} finally {
			try { if (pstmt != null) pstmt.close(); } catch (Exception ex) { }
			try { if (conn != null) conn.close(); } catch (Exception ex) { }
		}
		return count;
	}
}
